<?php
// +----------------------------------------------------------------------
// | Bwsaas
// +----------------------------------------------------------------------
// | Copyright (c) 2015~2020 http://www.buwangyun.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Gitee ( https://gitee.com/buwangyun/bwsaas )
// +----------------------------------------------------------------------
// | Author: buwangyun <hnlg666@163.com>
// +----------------------------------------------------------------------
// | Date: 2020-9-28 10:55:00
// +----------------------------------------------------------------------

namespace buwang\util;

use think\facade\Db;

/**
 * 读取Sql文件并返回可执行的sql语句
 */
class Sql
{
    /**
     * 从sql文件获取纯sql语句
     * @param string $sql_file sql文件路径
     * @param bool $string 如果为真，则只返回一条sql语句，默认以数组形式返回
     * @param array $replace 替换前缀，如：['my_' => 'me_']，表示将表前缀"my_"替换成"me_"
     *         这种前缀替换方法不一定准确，比如正常内容内有跟前缀相同的字符，也会被替换
     * @return mixed
     */
    public static function getSqlFromFile(string $sql_file = '', bool $string = false, array $replace = [], $prefix = "bw_")
    {
        if (!file_exists($sql_file)) {
            return false;
        }
        // 读取sql文件内容
        $handle = self::read_file($sql_file);
        // 分割语句
        return self::parseSql($handle, $string, $replace, $prefix);
    }

    /**
     * 分割sql语句
     * @param string $content sql内容
     * @param bool $string 如果为真，则只返回一条sql语句，默认以数组形式返回
     * @param array $replace 替换前缀，如：['my_' => 'me_']，表示将表前缀my_替换成me_
     * @return array|string 除去注释之后的sql语句数组或一条语句
     */
    public static function parseSql(string $content = '', bool $string = false, array $replace = [], $prefix = "bw_")
    {
        // 被替换的前缀
        $from = '';
        // 要替换的前缀
        $to = '';
        // 替换表前缀
        if (!empty($replace)) {
            $to = current($replace);
            $from = current(array_flip($replace));
        }
        if ($content != '') {
            // 纯sql内容
            $pure_sql = [];
            // 多行注释标记
            $comment = false;
            // 按行分割，兼容多个平台
            $content = str_replace(["\r\n", "\r"], "\n", $content);
            $content = explode("\n", trim($content));
            // 循环处理每一行
            foreach ($content as $key => $line) {
                // 跳过空行
                if ($line == '') {
                    continue;
                }
                // 跳过以#或者--开头的单行注释
                if (preg_match("/^(#|--)/", $line)) {
                    continue;
                }
                // 跳过以/**/包裹起来的单行注释
                if (preg_match("/^\/\*(.*?)\*\//", $line)) {
                    continue;
                }
                // 多行注释开始
                if (substr($line, 0, 2) == '/*') {
                    $comment = true;
                    continue;
                }
                // 多行注释结束
                if (substr($line, -2) == '*/') {
                    $comment = false;
                    continue;
                }
                // 多行注释没有结束，继续跳过
                if ($comment) {
                    continue;
                }
                // 替换表前缀
                if ($from != '') {
                    $line = str_replace('`' . $from, '`' . $to, $line);
                }
                //TODO 20230627 兼容老版本替换
                $line = str_replace('__BWPREFIX__', '', $line);
                //TODO 20230625 替换表前缀NEW
                if (strstr($line, 'CREATE TABLE')) {
                    $match_item = preg_match('/CREATE TABLE [`]?(\\w+)[`]?/is', $line, $match_data);
                } elseif (strstr($line, 'ALTER TABLE')) {
                    $match_item = preg_match('/ALTER TABLE [`]?(\\w+)[`]?/is', $line, $match_data);
                } elseif (strstr($line, 'INSERT INTO')) {
                    $match_item = preg_match('/INSERT INTO [`]?(\\w+)[`]?/is', $line, $match_data);
                } elseif (strstr($line, 'DELETE FROM')) {
                    $match_item = preg_match('/DELETE FROM [`]?(\\w+)[`]?/is', $line, $match_data);
                } elseif (strstr($line, "DROP TABLE IF EXISTS")) {
                    $match_item = preg_match('/DROP TABLE IF EXISTS [`]?(\\w+)[`]?/is', $line, $match_data);
                } else {
                    $match_item = 0;
                }
                if ($match_item > 0) {
                    $table_name = $match_data[1] ?? "";
                    $new_table_name = $prefix . $table_name;
                    $line = implode($new_table_name, explode($table_name, $line, 2));
                }
                // sql语句
                $pure_sql[] = $line;
            }

            // 只返回一条语句
            if ($string) {
                return implode('', $pure_sql);
            }

            // 以数组形式返回sql语句
            $pure_sql = implode("\n", $pure_sql);
            return explode(";\n", $pure_sql);
        } else {
            return $string ? '' : [];
        }
    }

    /**
     * 读取文件内容
     * @param string $filename 文件名
     * @return string 文件内容
     */
    public static function read_file(string $filename): string
    {
        $content = '';
        if (function_exists('file_get_contents')) {
            @$content = file_get_contents($filename);
        } else {
            if (@$fp = fopen($filename, 'r')) {
                @$content = fread($fp, filesize($filename));
                @fclose($fp);
            }
        }
        return $content;
    }


    /**
     * 判断对应表是否存在
     * @param $table_name
     * @return bool
     */
    public static function existTable($table_name): bool
    {
        //判断对应表是否存在
        $exist = Db::query("SHOW TABLES LIKE '{$table_name}'");
        return (bool)$exist;
    }


    /**
     * 导入SQL工具方法（简易文本读行方式）
     *
     * @param string $name 插件名称
     * @return  boolean
     */
    public static function importSql($sqlFile)
    {
        if (is_file($sqlFile)) {
            $lines = file($sqlFile);
            $templine = '';
            foreach ($lines as $line) {
                if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 2) == '/*') {
                    continue;
                }
                $templine .= $line;
                if (substr(trim($line), -1, 1) == ';') {
                    $templine = str_ireplace('__PREFIX__', config('database.prefix'), $templine);
                    $templine = str_ireplace('INSERT INTO ', 'INSERT IGNORE INTO ', $templine);
                    try {
                        Db::getPdo()->exec($templine);
                    } catch (\PDOException $e) {
                        //$e->getMessage();
                    }
                    $templine = '';
                }
            }
        }
        return true;
    }


    /**
     * rides连接
     */
    public static function redisConnect($host, $port, $name, $password = '')
    {
        try {
            //进行数据库连接
            $redis = new \Redis();
            //连接
            $redis->connect($host, $port);
            if ($password) $redis->auth($password);
            if (!$redis->ping()) {
                throw new \Exception('连接Redis失败！请检查连接参数!');
            }
            if (!$redis->select($name)) {
                throw new \Exception('连接Redis失败！数据库不存在!');
            }
        } catch (\Throwable $e) {
            throw new \Exception('连接Redis失败！请检查连接参数!');
        }
        return $redis;
    }

    /**
     * 数据库连接
     */
    public static function mysqlConnect($host, $user_name = '', $password = '', $database_name = '', $port = '')
    {
        $conn = @mysqli_connect($host, $user_name, $password, $database_name, $port);
        if (!$conn) throw new \Exception('数据库连接失败');
        return $conn;
    }
}
